﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using OctoOS;

/// <summary>
/// Summary description for DataSource
/// </summary>
public static class DataSource
{
    public static string _connectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;

    public static void ClassifiedResources_Save(
        long ResourceID,
        double latitude,
        double longitude,
        bool anonAds,
        byte comments,
        short autoExpNonAnonDays,
        short autoExpAnonDays,
        bool renewAds,
        bool adApprove,
        byte currency, 
        string adminMail)
    {
        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedResources_Save",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, ResourceID),
            DbUtil.CreateParameter("Latitude", SqlDbType.Float, latitude),
            DbUtil.CreateParameter("Longitude", SqlDbType.Float, longitude),
            DbUtil.CreateParameter("AllowAnonymousAds", SqlDbType.Bit, anonAds),
            DbUtil.CreateParameter("Comments", SqlDbType.TinyInt, comments),
            DbUtil.CreateParameter("AllowRenewal", SqlDbType.Bit, renewAds),
            DbUtil.CreateParameter("AutoExpireAnonymousDays", SqlDbType.SmallInt, autoExpAnonDays),
            DbUtil.CreateParameter("AutoExpireNonAnonymousDays", SqlDbType.SmallInt, autoExpNonAnonDays),
            DbUtil.CreateParameter("AdApprove", SqlDbType.Bit, adApprove),
            DbUtil.CreateParameter("CurrencyID", SqlDbType.TinyInt, currency),
            DbUtil.CreateParameter("AdminMail", SqlDbType.VarChar, adminMail == "" ? null : adminMail));
    }

    public static void ClassifiedResources_Update(
        long ResourceID,
        double latitude,
        double longitude,
        string adminMail)
    {
        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedResources_Update",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, ResourceID),
            DbUtil.CreateParameter("Latitude", SqlDbType.Float, latitude),
            DbUtil.CreateParameter("Longitude", SqlDbType.Float, longitude),
            DbUtil.CreateParameter("AdminMail", SqlDbType.VarChar, adminMail == "" ? null : adminMail));
    }

    public static Resource ClassifiedResources_Get(
        long resourceID)
    {
        Currency[] c = new Currency[] {};
        return ClassifiedResources_Get(
            resourceID,
            false,
            out c);
    }


    public static Resource ClassifiedResources_Get(
        long resourceID,
        bool needCurrency,
        out Currency[] currencies)
    {
        Resource resource = null;
        List<Currency> currList = new List<Currency>();
        using (var dataReader = DbUtil.OpenDataReader(
            _connectionString,
            "classified.ClassifiedResources_Get",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, resourceID),
            DbUtil.CreateParameter("NeedCurrency", SqlDbType.Bit, needCurrency)))
        {
            if (needCurrency)
            {
                // get currencies list
                while (dataReader.Read())
                {
                    currList.Add(
                        new Currency(
                            DbUtil.GetByte(dataReader, "CurrencyID"),
                            DbUtil.GetString(dataReader, "Code"),
                            DbUtil.GetString(dataReader, "Description")));
                }
                dataReader.NextResult();
            }
            currencies = currList.ToArray();
            // get resource settings
            if (dataReader.Read())
            {
                resource = new Resource(
                    resourceID,
                    DbUtil.GetDouble(dataReader, "Latitude"),
                    DbUtil.GetDouble(dataReader, "Longitude"),
                    DbUtil.GetBoolean(dataReader, "AllowAnonymousAds"),
                    DbUtil.GetByte(dataReader, "Comments"),
                    DbUtil.GetInt16(dataReader, "AutoExpireAnonymousDays"),
                    DbUtil.GetInt16(dataReader, "AutoExpireNonAnonymousDays"),
                    DbUtil.GetBoolean(dataReader, "AllowRenewal"),
                    DbUtil.GetBoolean(dataReader, "AdApprove"),
                    DbUtil.GetByte(dataReader, "CurrencyID"),
                    DbUtil.GetString(dataReader, "AdminMail", ""),
                    DbUtil.GetBoolean(dataReader, "IsMaster"));
            }
            else
            {
                dataReader.NextResult();
                if (dataReader.Read())
                {
                    resource = new Resource(
                    resourceID,
                    DbUtil.GetDouble(dataReader, "Latitude"),
                    DbUtil.GetDouble(dataReader, "Longitude"),
                    DbUtil.GetBoolean(dataReader, "AllowAnonymousAds"),
                    DbUtil.GetByte(dataReader, "Comments"),
                    DbUtil.GetInt16(dataReader, "AutoExpireAnonymousDays"),
                    DbUtil.GetInt16(dataReader, "AutoExpireNonAnonymousDays"),
                    DbUtil.GetBoolean(dataReader, "AllowRenewal"),
                    DbUtil.GetBoolean(dataReader, "AdApprove"),
                    DbUtil.GetByte(dataReader, "CurrencyID"),
                    DbUtil.GetString(dataReader, "AdminMail", ""),
                    DbUtil.GetBoolean(dataReader, "IsMaster"));
                }
                dataReader.NextResult();
                if (dataReader.Read())
                {
                    if (resource != null)
                    {
                        resource.SetAdminMail(DbUtil.GetString(dataReader, "AdminMail", ""));
                        resource.SetLatitude(DbUtil.GetDouble(dataReader, "Latitude"));
                        resource.SetLongitude(DbUtil.GetDouble(dataReader, "Longitude"));
                    }
                }

            }
        }
        return resource;

    }

    public static void ClassifiedCategories_Save(
        long ResourceID,
        long CategoryID,
        long? ParentCategoryID,
        string CategoryName,
        string AdNameExample,
        string ImageUrl,
        string RedirectUrl,
        bool readOnly)
    {
        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedCategories_Save",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, ResourceID),
            DbUtil.CreateParameter("CategoryID", SqlDbType.BigInt, CategoryID),
            DbUtil.CreateParameter("ParentCategoryID", SqlDbType.BigInt, ParentCategoryID <= 0 ? null : ParentCategoryID),
            DbUtil.CreateParameter("CategoryName", SqlDbType.VarChar, CategoryName),
            DbUtil.CreateParameter("AdNameExample", SqlDbType.VarChar, AdNameExample == "" ? null : AdNameExample),
            DbUtil.CreateParameter("ImageUrl", SqlDbType.VarChar, ImageUrl == "" ? null : ImageUrl),
            DbUtil.CreateParameter("RedirectUrl", SqlDbType.VarChar, RedirectUrl == "" ? null : RedirectUrl),
            DbUtil.CreateParameter("Time", SqlDbType.DateTime, DateTime.Now),
            DbUtil.CreateParameter("ReadOnly", SqlDbType.Bit, readOnly));
    }



     public static void ClassifiedAttributes_Save(
        long categoryID,
        long attributeID,
        string attributeTitle,
        string attributeDescription,
        bool required, 
        bool isCustom,
        AttributeValueEntity[] values)
    {
        DataTable table = new DataTable("AttributeValueNames");
        table.Columns.Add("AttributeValue", typeof(Int32));
        table.Columns.Add("AttributeValueName");

        foreach (var value in values)
        {
            var row = table.NewRow();
            row[0] = value.AttributeValue;
            row[1] = value.AttributeValueName;
            table.Rows.Add(row);
        }

        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedAttributes_Save",
            DbUtil.CreateParameter("CategoryID", SqlDbType.BigInt, categoryID),
            DbUtil.CreateParameter("AttributeID", SqlDbType.BigInt, attributeID),
            DbUtil.CreateParameter("AttributeTitle", SqlDbType.VarChar, attributeTitle ),
            DbUtil.CreateParameter("AttributeDescription", SqlDbType.VarChar, attributeDescription == "" ? null : attributeDescription),
            DbUtil.CreateParameter("Required", SqlDbType.Bit, required),
            DbUtil.CreateParameter("IsCustom", SqlDbType.Bit, isCustom),
            DbUtil.CreateParameter("AttributeValueNames", table));
    }


    public static Attribute ClassifiedAttributes_GetByID(
        long categoryID,
        long attributeID,
        out int maxValue)
    {
        Attribute attribute = null;
        SqlParameter output = DbUtil.CreateOutputParameter("MaxAttributeValue", SqlDbType.TinyInt);
        using (var dataReader = DbUtil.OpenDataReader(
            _connectionString,
            "classified.ClassifiedAttributes_GetByID",
            DbUtil.CreateParameter("CategoryID", SqlDbType.BigInt, categoryID),
            DbUtil.CreateParameter("AttributeID", SqlDbType.BigInt, attributeID),
            output
            ))
        {
            List<AttributeValueEntity> values = new List<AttributeValueEntity>();
            while (dataReader.Read())
            {
                values.Add(
                    new AttributeValueEntity(
                        attributeID,
                        DbUtil.GetByte(dataReader, "AttributeValue"),
                        DbUtil.GetString(dataReader, "AttributeValueName")));
            }

            dataReader.NextResult();

            if (dataReader.Read())
            {
                attribute = new Attribute
                (
                    attributeID,
                    categoryID,
                    DbUtil.GetString(dataReader, "AttributeTitle"),
                    DbUtil.GetString(dataReader, "AttributeDescription", ""),
                    DbUtil.GetBoolean(dataReader, "Required"),
                    DbUtil.GetByte(dataReader, "SortOrder"),
                    values.ToArray(),
                    DbUtil.GetBoolean(dataReader, "IsCustom")
                );
            }

            
        }

        maxValue = Convert.ToInt32(output.Value);

        return attribute;

    }

    public static Attribute[] ClassifiedAttributes_GetByCategory(
        long categoryID)
    {
        List<Attribute> attributes = new List<Attribute>();
        SqlParameter output = DbUtil.CreateOutputParameter("MaxAttributeValue", SqlDbType.TinyInt);
        using (var dataReader = DbUtil.OpenDataReader(
            _connectionString,
            "classified.ClassifiedAttributes_GetByID",
            DbUtil.CreateParameter("CategoryID", SqlDbType.BigInt, categoryID),
            DbUtil.CreateParameter("AttributeID", SqlDbType.BigInt, 0),
            output
            ))
        {
            List<AttributeValueEntity> values = new List<AttributeValueEntity>();
            while (dataReader.Read())
            {
                values.Add(
                    new AttributeValueEntity(
                        DbUtil.GetInt64(dataReader, "AttributeID"),
                        DbUtil.GetByte(dataReader, "AttributeValue"),
                        DbUtil.GetString(dataReader, "AttributeValueName")));
            }

            dataReader.NextResult();

            while (dataReader.Read())
            {
                attributes.Add(new Attribute
                (
                    DbUtil.GetInt64(dataReader, "AttributeID"),
                    categoryID,
                    DbUtil.GetString(dataReader, "AttributeTitle"),
                    DbUtil.GetString(dataReader, "AttributeDescription", ""),
                    DbUtil.GetBoolean(dataReader, "Required"),
                    DbUtil.GetByte(dataReader, "SortOrder"),
                    values.FindAll(f=>f.AttributeID ==DbUtil.GetInt64(dataReader, "AttributeID")).ToArray(),
                    DbUtil.GetBoolean(dataReader, "IsCustom")
                ));
            }


        }

        return attributes.ToArray();

    }

    public static void ClassifiedAttributes_Delete(
        long CategoryID,
        long AttributeID)
    {
        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedAttributes_Delete",
            DbUtil.CreateParameter("CategoryID", SqlDbType.BigInt, CategoryID),
            DbUtil.CreateParameter("AttributeID", SqlDbType.BigInt, AttributeID));
    }

    public static void ClassifiedAttributeValues_Delete(
        long categoryID,
        long attributeID,
        short attributeValue)
    {
        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedAttributeValues_Delete",
            DbUtil.CreateParameter("CategoryID", SqlDbType.BigInt, categoryID),
            DbUtil.CreateParameter("AttributeID", SqlDbType.BigInt, attributeID),
            DbUtil.CreateParameter("AttributeValue", SqlDbType.TinyInt, attributeValue)
            );
    }


    public static void ClassifiedCategories_Delete(
        long resourceID,
        long categoryID)
    {
        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedCategories_Delete",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, resourceID),
            DbUtil.CreateParameter("CategoryID", SqlDbType.BigInt, categoryID));
    }

    public static void ClassifiedAttributes_ChangeOrder(
        long categoryID,
        long prevAttrID,
        long nextAttrID)
    {
        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedAttributes_ChangeOrder",
            DbUtil.CreateParameter("CategoryID", SqlDbType.BigInt, categoryID),
            DbUtil.CreateParameter("PrevAttributeID", SqlDbType.BigInt, prevAttrID),
            DbUtil.CreateParameter("NextAttributeID", SqlDbType.BigInt, nextAttrID));
    }


    public static Category[] ClassifiedCategories_GetByVersion(
        long resourceID,
        int versionCache,
        out int versionDB)
    {
        SqlParameter versionDbParam = DbUtil.CreateOutputParameter("VersionDB", SqlDbType.Int);
        List<Category> allCategories = new List<Category>();
        using (var dataReader = DbUtil.OpenDataReader(
            _connectionString,
            "classified.ClassifiedCategories_GetByVersion",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, resourceID),
            DbUtil.CreateParameter("VersionCache", SqlDbType.BigInt, versionCache),
            versionDbParam
            ))
        {
            while (dataReader.Read())
            {
                var cat = new Category
                (
                    DbUtil.GetInt64(dataReader, "CategoryID"),
                    DbUtil.GetString(dataReader, "CategoryName"),
                    DbUtil.GetInt64(dataReader, "ParentCategoryID", 0),
                    DbUtil.GetString(dataReader, "AdNameExample", ""),
                    DbUtil.GetString(dataReader, "ImageUrl", ""),
                    DbUtil.GetString(dataReader, "RedirectUrl", ""),
                    DbUtil.GetBoolean(dataReader, "ReadOnly"),
                    new Attribute[] { }
                );

                allCategories.Add(cat);
            }
        }

        versionDB = Convert.ToInt32(versionDbParam.Value);

        return allCategories.ToArray();
    }


    public static void ClassifiedAds_Save(
        long resourceID,
        long categoryID,
        long adID,
        string title,
        string adText,
        DateTime expTime,
        byte textFormat,
        bool searching,
        double latitude,
        double longitude,
        byte priceType,
        double? price,
        string phone,
        string email, 
        string url,
        long userID,
        long userComputerID,
        byte[] userIP,
        AttributeValueEntity[] attributes,
        AttributeValueEntity[] customAttributes,
        byte status
        )
    {
        // attributes
        DataTable tableAttr = new DataTable("AttributeValues");
        tableAttr.Columns.Add("AttributeID", typeof(Int64));
        tableAttr.Columns.Add("AttributeValue", typeof(Byte));

        foreach (var value in attributes)
        {
            var row = tableAttr.NewRow();
            row[0] = value.AttributeID;
            row[1] = value.AttributeValue;
            tableAttr.Rows.Add(row);
        }

        // custom attributes
        DataTable tableAttrCustom = new DataTable("CustomAttributeValues");
        tableAttrCustom.Columns.Add("AttributeID", typeof(Int64));
        tableAttrCustom.Columns.Add("AttributeValueName");

        foreach (var value in customAttributes)
        {
            var row = tableAttrCustom.NewRow();
            row[0] = value.AttributeID;
            row[1] = value.AttributeValueName;
            tableAttrCustom.Rows.Add(row);
        }


        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedAds_Save",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, resourceID),
            DbUtil.CreateParameter("CategoryID", SqlDbType.BigInt, categoryID),
            DbUtil.CreateParameter("AdID", SqlDbType.BigInt, adID),
            DbUtil.CreateParameter("Latitude", SqlDbType.Float, latitude),
            DbUtil.CreateParameter("Longitude", SqlDbType.Float, longitude),
            DbUtil.CreateParameter("Title", SqlDbType.NVarChar, title),
            DbUtil.CreateParameter("AdText", SqlDbType.NVarChar, adText),
            DbUtil.CreateParameter("TextFormat", SqlDbType.TinyInt, textFormat),
            DbUtil.CreateParameter("Searching", SqlDbType.Bit, searching),
            DbUtil.CreateParameter("ExpiryTime", SqlDbType.SmallDateTime, expTime),
            DbUtil.CreateParameter("PriceType", SqlDbType.TinyInt, priceType),
            DbUtil.CreateParameter("Price", SqlDbType.Float, price == 0 ? null : price),
            DbUtil.CreateParameter("ContactPhone", SqlDbType.VarChar, phone == "" ? null : phone),
            DbUtil.CreateParameter("ContactEmail", SqlDbType.VarChar, email == "" ? null : email),
            DbUtil.CreateParameter("ContactUrl", SqlDbType.VarChar, url == "" ? null : url),
            DbUtil.CreateParameter("UserID", SqlDbType.VarChar, userID),
            DbUtil.CreateParameter("userComputerID", SqlDbType.VarChar, userComputerID),
            DbUtil.CreateParameter("userIP", SqlDbType.VarBinary, userIP),
            DbUtil.CreateParameter("AttributeValues", tableAttr),
            DbUtil.CreateParameter("CustomAttributeValues", tableAttrCustom),
            DbUtil.CreateParameter("Status", status)
            );
    }



    public static ClassifiedAd ClassifiedAds_GetByID(
        long resourceID,
        long categoryID,
        long adID)
    {
        using (var dataReader = DbUtil.OpenDataReader(
            _connectionString,
            "classified.ClassifiedAds_GetByID",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, resourceID),
            DbUtil.CreateParameter("CategoryID", SqlDbType.BigInt, categoryID),
            DbUtil.CreateParameter("AdID", SqlDbType.BigInt, adID)))
        {
            ClassifiedAd ad = null;
            List<SelectedAttribute> attrValuesSelected = new List<SelectedAttribute>();
            List<AttributeValueEntity> attrValues = new List<AttributeValueEntity>();
            List<Attribute> attributes = new List<Attribute>();

            // written custon attribute values
            while (dataReader.Read())
            {
                attrValues.Add(
                    new AttributeValueEntity(
                        DbUtil.GetInt64(dataReader, "AttributeID"),
                        0,
                        DbUtil.GetString(dataReader, "AttributeValueName")));
            }
            dataReader.NextResult();

            // chosen attribute values
            while (dataReader.Read())
            {
                attrValuesSelected.Add(
                    new SelectedAttribute
                    { AttributeID = DbUtil.GetInt64(dataReader, "AttributeID"),
                      AttributeValue = DbUtil.GetByte(dataReader, "AttributeValue")});
            }

            dataReader.NextResult();

            // all attribute values
            while (dataReader.Read())
            {
                byte value = DbUtil.GetByte(dataReader, "AttributeValue");
                long attrID = DbUtil.GetInt64(dataReader, "AttributeID");
                attrValues.Add(
                    new AttributeValueEntity
                    (
                        attrID,
                        value,
                        DbUtil.GetString(dataReader, "AttributeValueName"),
                        attrValuesSelected.Find(f=>f.AttributeValue == value && f.AttributeID == attrID)!=null
                    ));
            }

            dataReader.NextResult();

            // attributes

            while (dataReader.Read())
            {
                long attrID = DbUtil.GetInt64(dataReader, "AttributeID");
                List<AttributeValueEntity> values = new List<AttributeValueEntity>();
                values = attrValues.FindAll(f => f.AttributeID == attrID);

                attributes.Add(
                    new Attribute(
                        attrID,
                        DbUtil.GetInt64(dataReader, "CategoryID"),
                        DbUtil.GetString(dataReader, "AttributeTitle"),
                        DbUtil.GetString(dataReader, "AttributeDescription", ""),
                        DbUtil.GetBoolean(dataReader, "Required"),
                        DbUtil.GetByte(dataReader, "SortOrder"),
                        values.ToArray(),
                        DbUtil.GetBoolean(dataReader, "IsCustom")));
            }

            dataReader.NextResult();
            //ad
            if (dataReader.Read())
            {
                ad = new ClassifiedAd
                (
                    adID,
                    DbUtil.GetInt64(dataReader, "CategoryID"),
                    DbUtil.GetString(dataReader, "Title"),
                    DbUtil.GetString(dataReader, "AdText"),
                    DbUtil.GetDateTime(dataReader, "ExpiryTime"),
                    DbUtil.GetDateTime(dataReader, "PostTime"),
                    DbUtil.GetByte(dataReader, "PriceType"),
                    Convert.ToDouble(DbUtil.GetDecimal(dataReader, "Price", 0)),
                    DbUtil.GetBoolean(dataReader, "Searching"),
                    DbUtil.GetByte(dataReader, "TextFormat"),
                    DbUtil.GetDouble(dataReader, "Latitude", 0),
                    DbUtil.GetDouble(dataReader, "Longitude", 0),
                    DbUtil.GetString(dataReader, "ContactPhone", ""),
                    DbUtil.GetString(dataReader, "ContactEmail", ""),
                    DbUtil.GetString(dataReader, "ContactUrl", ""),
                    attributes.ToArray(),
                    DbUtil.GetByte(dataReader, "Status"),
                    DbUtil.GetString(dataReader, "Description", ""),
                    DbUtil.GetInt64(dataReader, "UserID")
                );
            }
            
            if (ad == null)
            {
                // try to get from expired ads
                dataReader.NextResult();
                if (dataReader.Read())
                {
                    ad = new ClassifiedAd
                    (
                        adID,
                        DbUtil.GetInt64(dataReader, "CategoryID"),
                        DbUtil.GetString(dataReader, "Title"),
                        DbUtil.GetString(dataReader, "AdText"),
                        DbUtil.GetDateTime(dataReader, "ExpiryTime"),
                        DbUtil.GetDateTime(dataReader, "PostTime"),
                        DbUtil.GetByte(dataReader, "PriceType"),
                        Convert.ToDouble(DbUtil.GetDecimal(dataReader, "Price", 0)),
                        DbUtil.GetBoolean(dataReader, "Searching"),
                        DbUtil.GetByte(dataReader, "TextFormat"),
                        DbUtil.GetDouble(dataReader, "Latitude", 0),
                        DbUtil.GetDouble(dataReader, "Longitude", 0),
                        DbUtil.GetString(dataReader, "ContactPhone", ""),
                        DbUtil.GetString(dataReader, "ContactEmail", ""),
                        DbUtil.GetString(dataReader, "ContactUrl", ""),
                        attributes.ToArray(),
                        (byte)Helper.AdStatus.Expired,
                        "",
                        DbUtil.GetInt64(dataReader, "UserID")
                    );
                }
            }

            if (adID == 0)
            {
                ad = new ClassifiedAd(attributes.ToArray());
            }

            dataReader.NextResult();
            // resource settings
            byte currID = 0;
            if (ad != null)
            {
                if (dataReader.Read())
                {
                    ad.SetAllowRenewal(DbUtil.GetBoolean(dataReader, "AllowRenewal"));
                    ad.SetAnonExpDays(DbUtil.GetInt16(dataReader, "AutoExpireAnonymousDays"));
                    ad.SetNonAnonExpDays(DbUtil.GetInt16(dataReader, "AutoExpireNonAnonymousDays"));
                    ad.SetComments(DbUtil.GetByte(dataReader, "Comments"));
                    ad.SetApprove(DbUtil.GetBoolean(dataReader, "AdApprove"));
                    currID = DbUtil.GetByte(dataReader, "CurrencyID");
                }
                else
                {
                    dataReader.NextResult();
                    if (dataReader.Read())
                    {
                        ad.SetAllowRenewal(DbUtil.GetBoolean(dataReader, "AllowRenewal"));
                        ad.SetAnonExpDays(DbUtil.GetInt16(dataReader, "AutoExpireAnonymousDays"));
                        ad.SetNonAnonExpDays(DbUtil.GetInt16(dataReader, "AutoExpireNonAnonymousDays"));
                        ad.SetComments(DbUtil.GetByte(dataReader, "Comments"));
                        ad.SetApprove(DbUtil.GetBoolean(dataReader, "AdApprove"));
                        currID = DbUtil.GetByte(dataReader, "CurrencyID");
                    }
                }
            }

            
            dataReader.NextResult();
            //ad currency
            if (ad != null)
            {
                while (dataReader.Read())
                {
                    if (DbUtil.GetByte(dataReader, "CurrencyID") == currID)
                    {
                        ad.SetCurrencyCode(DbUtil.GetString(dataReader, "Code"));
                        continue;
                    }
                }
            }
            return ad;
        }

        
    }


    public static ClassifiedAd[] ClassifiedAds_GetByCategory(
        long resourceID,
        long[] categories)
    {

        DataTable table = new DataTable("Categories");
        table.Columns.Add("CategoryID", typeof(Int64));

        foreach (var value in categories)
        {
            var row = table.NewRow();
            row[0] = value;
            table.Rows.Add(row);
        }

        using (var dataReader = DbUtil.OpenDataReader(
            _connectionString,
            "classified.ClassifiedAds_GetByCategory",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, resourceID),
            DbUtil.CreateParameter("Categories", table)))
        {

            List<ClassifiedAd> ads = new List<ClassifiedAd>();
            //ad
            while (dataReader.Read())
            {
                ads.Add(new ClassifiedAd
                (
                    DbUtil.GetInt64(dataReader, "AdID"),
                    DbUtil.GetInt64(dataReader, "CategoryID"),
                    DbUtil.GetString(dataReader, "Title"),
                    DbUtil.GetString(dataReader, "AdText"),
                    DbUtil.GetDateTime(dataReader, "ExpiryTime"),
                    DbUtil.GetDateTime(dataReader, "PostTime"),
                    DbUtil.GetByte(dataReader, "PriceType"),
                    Convert.ToDouble(DbUtil.GetDecimal(dataReader, "Price", 0)),
                    DbUtil.GetBoolean(dataReader, "Searching"),
                    DbUtil.GetByte(dataReader, "TextFormat"),
                    DbUtil.GetDouble(dataReader, "Latitude", 0),
                    DbUtil.GetDouble(dataReader, "Longitude", 0),
                    DbUtil.GetString(dataReader, "ContactPhone", ""),
                    DbUtil.GetString(dataReader, "ContactEmail", ""),
                    DbUtil.GetString(dataReader, "ContactUrl", ""),
                    null,
                    DbUtil.GetByte(dataReader, "Status"),
                    DbUtil.GetString(dataReader, "Description"),
                    DbUtil.GetInt64(dataReader, "UserID")
                    ));
            }
            return ads.ToArray();
        }
    }

    public static ClassifiedAd[] ClassifiedAds_Search(
        long resourceID,
        bool isRadios,
        int? miles,
        byte adType,
        bool isPictures,
        SelectedAttribute[] attributes,
        byte sortMode,
        long[] categories, 
        int startIndex,
        int adsOnPage,
        out int totalCnt)
    {

        DataTable table = new DataTable("Categories");
        table.Columns.Add("CategoryID", typeof(Int64));

        foreach (var value in categories)
        {
            var row = table.NewRow();
            row[0] = value;
            table.Rows.Add(row);
        }

        DataTable tableAtt = new DataTable("searchattributes");
        tableAtt.Columns.Add("AttributeID", typeof(Int64));
        tableAtt.Columns.Add("AttributeValue", typeof(Byte));

        foreach (var value in attributes)
        {
            var row = tableAtt.NewRow();
            row[0] = value.AttributeID;
            row[1] = value.AttributeValue;
            tableAtt.Rows.Add(row);
        }

        using (var dataReader = DbUtil.OpenDataReader(
            _connectionString,
            "classified.ClassifiedAds_Search",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, resourceID),
            DbUtil.CreateParameter("IsRadios", SqlDbType.Bit, isRadios),
            DbUtil.CreateParameter("Miles", SqlDbType.Int, miles),
            DbUtil.CreateParameter("AdType", SqlDbType.TinyInt, adType),
            DbUtil.CreateParameter("Pictures", SqlDbType.Bit, isPictures),
            DbUtil.CreateParameter("SortMode", SqlDbType.TinyInt, sortMode),
            DbUtil.CreateParameter("SearchAtt",  tableAtt),
            DbUtil.CreateParameter("Categories", table),
            DbUtil.CreateParameter("StartIndex", SqlDbType.Int, startIndex),
            DbUtil.CreateParameter("AdsOnPage", SqlDbType.Int, adsOnPage)
            ))
        {

            if (dataReader.Read())
            {
                totalCnt = DbUtil.GetInt32(dataReader, "cnt");
            }
            else
            {
                totalCnt = 0;
            }

            dataReader.NextResult();
            //ads
            List<ClassifiedAd> ads = new List<ClassifiedAd>();
            
            while (dataReader.Read())
            {
                ads.Add(new ClassifiedAd
                (
                    DbUtil.GetInt64(dataReader, "AdID"),
                    DbUtil.GetInt64(dataReader, "CategoryID"),
                    DbUtil.GetString(dataReader, "Title"),
                    DbUtil.GetString(dataReader, "AdText")
                ));
            }
            return ads.ToArray();
        }
    }

    public static void ClassifiedAds_Renew(
        long resourceID,
        long adID,
        int days)
    {
        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedAds_Renew",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, OOSRequest.Current.ResourceID),
            DbUtil.CreateParameter("AdID", SqlDbType.BigInt, adID),
            DbUtil.CreateParameter("AddDays", SqlDbType.Int, days));
    }

    public static void ClassifiedAttributes_Sort(
        string attributeOrder)
    {
        DataTable table = new DataTable("Attributes");
        table.Columns.Add("AttributeID", typeof(Int64));
        table.Columns.Add("AttributeValue", typeof(Byte));

        attributeOrder = attributeOrder.Substring(1);
        string[] attOrdered = attributeOrder.Split(new char[] { ',' });
        for (int i = 0; i < attOrdered.Count(); i++)
        {
            DataRow row = table.NewRow();
            row[0] = attOrdered[i];
            row[1] = i + 1;
            table.Rows.Add(row);
        }

            DbUtil.ExecuteNonQuery(
                _connectionString,
                "classified.ClassifiedAttributes_Sort",
                DbUtil.CreateParameter("Attributes", table));
    }

    public static ClassifiedAd[] ClassifiedAds_GetMyNonExpired(
        long resourceID,
        long userID,
        byte adType,
        byte status,
        int startIndex,
        int adsOnPage,
        out int totalActive,
        out int totalPending,
        out int totalHidden,
        out int totalExpired
        )
    {
        totalActive = totalPending = totalHidden = totalExpired = 0;
        using (var dataReader = DbUtil.OpenDataReader(
            _connectionString,
            "classified.ClassifiedAds_GetMyNonExpired",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, resourceID),
            DbUtil.CreateParameter("UserID", SqlDbType.BigInt, userID),
            DbUtil.CreateParameter("AdType", SqlDbType.TinyInt, adType),
            DbUtil.CreateParameter("AdStatus", SqlDbType.TinyInt, status),
            DbUtil.CreateParameter("StartIndex", SqlDbType.Int, startIndex),
            DbUtil.CreateParameter("AdsOnPage", SqlDbType.Int, adsOnPage)))
        {
            // active, pending and hidden ads count
            while (dataReader.Read())
            {
                if (DbUtil.GetByte(dataReader, "Status") == (byte)Helper.AdStatus.Active)
                {
                    totalActive = DbUtil.GetInt32(dataReader, "Cnt");
                }
                else if (DbUtil.GetByte(dataReader, "Status") == (byte)Helper.AdStatus.Pending)
                {
                    totalPending = DbUtil.GetInt32(dataReader, "Cnt");
                }
                else 
                {
                    totalHidden = DbUtil.GetInt32(dataReader, "Cnt");
                }
            }

            // expired ads count
            dataReader.NextResult();
            if (dataReader.Read())
            {
                totalExpired = DbUtil.GetInt32(dataReader, "Cnt");
            }

            // ads
            dataReader.NextResult();

            List<ClassifiedAd> ads = new List<ClassifiedAd>();
            while (dataReader.Read())
            {
                ads.Add(
                    new ClassifiedAd(
                        DbUtil.GetInt64(dataReader, "AdID"),
                        DbUtil.GetInt64(dataReader, "CategoryID"),
                        DbUtil.GetString(dataReader, "Title"),
                        DbUtil.GetDateTime(dataReader, "PostTime")));
            }

            return ads.ToArray();

        }
    }

    public static void ClassifiedAds_Delete(
        long adID)
    {
        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedAds_Delete",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, OOSRequest.Current.ResourceID),
            DbUtil.CreateParameter("AdID", SqlDbType.BigInt, adID));
    }

    public static void ClassifiedAds_SetStatus(
        long adID,
        byte status,
        string description)
    {
        DbUtil.ExecuteNonQuery(
            _connectionString,
            "classified.ClassifiedAds_SetStatus",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, OOSRequest.Current.ResourceID),
            DbUtil.CreateParameter("AdID", SqlDbType.BigInt, adID),
            DbUtil.CreateParameter("AdStatus", SqlDbType.TinyInt, status),
            DbUtil.CreateParameter("Description", SqlDbType.VarChar, description));
    }

    public static ClassifiedAd[] ClassifiedAds_GetMyExpired(
        long resourceID,
        long userID,
        byte adType,
        int startIndex,
        int adsOnPage,
        out int totalActive,
        out int totalPending,
        out int totalHidden,
        out int totalExpired
        )
    {
        totalActive = totalHidden = totalExpired = totalPending = 0;
        using (var dataReader = DbUtil.OpenDataReader(
            _connectionString,
            "classified.ClassifiedAds_GetMyExpired",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, resourceID),
            DbUtil.CreateParameter("UserID", SqlDbType.BigInt, userID),
            DbUtil.CreateParameter("AdType", SqlDbType.TinyInt, adType),
            DbUtil.CreateParameter("StartIndex", SqlDbType.Int, startIndex),
            DbUtil.CreateParameter("AdsOnPage", SqlDbType.Int, adsOnPage)))
        {
            // active, pending and hidden ads count
            while (dataReader.Read())
            {
                if (DbUtil.GetByte(dataReader, "Status") == (byte)Helper.AdStatus.Active)
                {
                    totalActive = DbUtil.GetInt32(dataReader, "Cnt");
                }
                else if (DbUtil.GetByte(dataReader, "Status") == (byte)Helper.AdStatus.Pending)
                {
                    totalPending = DbUtil.GetInt32(dataReader, "Cnt");
                }
                else 
                {
                    totalHidden = DbUtil.GetInt32(dataReader, "Cnt");
                }
            }

            // expired ads count
            dataReader.NextResult();
            if (dataReader.Read())
            {
                totalExpired = DbUtil.GetInt32(dataReader, "Cnt");
            }

            dataReader.NextResult();

            List<ClassifiedAd> ads = new List<ClassifiedAd>();
            while (dataReader.Read())
            {
                ads.Add(
                    new ClassifiedAd(
                        DbUtil.GetInt64(dataReader, "AdID"),
                        DbUtil.GetInt64(dataReader, "CategoryID"),
                        DbUtil.GetString(dataReader, "Title"),
                        DbUtil.GetDateTime(dataReader, "PostTime")));
            }

            return ads.ToArray();

        }
    }


    public static ClassifiedAd[] ClassifiedAds_GetByStatus(
        long resourceID,
        byte status,
        int startIndex,
        int adsOnPage,
        out int totalAds
        )
    {
        totalAds = 0;
        List<ClassifiedAd> list = new List<ClassifiedAd>();
        using (var dataReader = DbUtil.OpenDataReader(
            _connectionString,
            "classified.ClassifiedAds_GetByStatus",
            DbUtil.CreateParameter("ResourceID", SqlDbType.BigInt, resourceID),
            DbUtil.CreateParameter("AdStatus", SqlDbType.TinyInt, status),
            DbUtil.CreateParameter("StartIndex", SqlDbType.Int, startIndex),
            DbUtil.CreateParameter("AdsOnPage", SqlDbType.Int, adsOnPage)))
        {
            if (dataReader.Read())
            {
                totalAds = DbUtil.GetInt32(dataReader, "Cnt");
            }

            dataReader.NextResult();

            while (dataReader.Read())
            {
                list.Add(
                    new ClassifiedAd(
                        DbUtil.GetInt64(dataReader, "AdID"),
                        DbUtil.GetInt64(dataReader, "CategoryID"),
                        DbUtil.GetString(dataReader, "Title"),
                        DbUtil.GetDateTime(dataReader, "PostTime")));
            }

            return list.ToArray();
        }
    }
}

